﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 建邦配料系统
{
    public partial class UserManagement : Form
    {
        public UserManagement()
        {
            InitializeComponent();
        }
        static String connetStr = "server=127.0.0.1;port=3306;user=root;password=952716; database=MySQL";
        //usr:用户名，password：数据库密码，database：数据库名
        MySqlConnection conn = new MySqlConnection(connetStr);

        private async void UserManagement_Load(object sender, EventArgs e)
        {
            await ProjectTableAsync();

        }

        #region 读取项目表到GUI中
        public async Task ProjectTableAsync()
        {
            using (MySqlConnection conn = new MySqlConnection(connetStr))
            {
                string sql = "select * from ingredientdb.loginmanagement";
                await conn.OpenAsync();
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader reader = cmd.ExecuteReader();//执行ExecuteReader()返回一个MySqlDataReader对象
                while (reader.Read())
                {
                    int index = this.dataGridView1.Rows.Add();
                    this.dataGridView1.Rows[index].Cells[0].Value = reader.GetString("UserName");
                    this.dataGridView1.Rows[index].Cells[1].Value = reader.GetString("Password");
                    this.dataGridView1.Rows[index].Cells[2].Value = reader.GetString("Permission");
                }
            }
        }
        #endregion

        private void button1_Click(object sender, EventArgs e)
        {
            using (MySqlConnection conn = new MySqlConnection(connetStr))
            {
                string sql = @"TRUNCATE TABLE ingredientdb.loginmanagement;";
                conn.Open();
                //执行sql命令   
                MySqlCommand command = new MySqlCommand(sql, conn);
                int n = command.ExecuteNonQuery();  //使用命令返回数据
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    try
                    {
                        string sql1 = @"INSERT INTO ingredientdb.loginmanagement(UserName,Password,Permission) VALUES  ('" + dataGridView1.Rows[i].Cells[0].Value.ToString().Trim() + "' , '" + dataGridView1.Rows[i].Cells[1].Value.ToString().Trim() + "','" + dataGridView1.Rows[i].Cells[2].Value.ToString().Trim() + "');";
                        //执行sql命令
                        MySqlCommand command1 = new MySqlCommand(sql1, conn);
                        int n1 = command1.ExecuteNonQuery();  //使用命令返回数据
                    }
                    catch (Exception ex) { }
                }
            }
            MessageBox.Show("保存成功！");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.dataGridView1.Rows.Add();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            dataGridView1.Rows.RemoveAt(dataGridView1.CurrentRow.Index);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}
